# _*_ coding:utf-8 _*_
# @File  : user.py
# @Time  : 2021-10-26  16:07
# @Author: zizle
import datetime

import pandas as pd
from fastapi import APIRouter, Body, Query
from pydantic import BaseModel

from hutool import security
from hutool.utils import create_random_string
from status import r_status
from db import FAConnection


admin_user_api = APIRouter()


class UserItem(BaseModel):
    username: str
    phone: str = ''
    email: str
    password: str
    role: str
    note: str = ''
    is_active: int = 1


@admin_user_api.post('/', summary='创建一个用户')
async def create_user(user_item: UserItem = Body(...)):
    # 解密得到account和password
    phone = security.rsa_decrypt(user_item.phone, key_tuple=0)
    # 随意验证一下手机
    if len(phone) != 11 or not phone.isdigit():
        return {'code': r_status.VALIDATE_ERROR, 'message': '手机号格式错误!'}
    password = security.rsa_decrypt(user_item.password, key_tuple=0)
    password = password if password else phone[-6:]
    hash_password = security.cipher_user_password(password)
    user_code = 'Rui_' + create_random_string(10)
    username = user_code if not user_item.username else user_item.username
    sql = 'INSERT INTO user_user (user_code,username,phone,email,password_hashed,role,note) ' \
          'VALUES (%s,%s,%s,%s,%s,%s,%s);'
    db = FAConnection(conn_name='添加用户')

    # 查询手机号是否存在,存在不能注册
    is_exists = db.query(sql='SELECT id FROM user_user WHERE phone=%s LIMIT 1;', param=[phone], keep_conn=True)
    if is_exists:
        db.close()
        return {'code': r_status.CONTENT_EXISTS, 'message': '手机号已存在,请直接登录.'}

    count, new_id = db.insert(sql, [user_code, username, phone, user_item.email, hash_password, user_item.role,
                              user_item.note])
    if count:
        # 添加ruizy_user用户表
        insert_sql = 'INSERT INTO ruizy_user' \
                     '(id,ruizy_code,nickname,phone,password,expire,role,vip_expire,note) VALUES ' \
                     '(%(id)s,%(ruizy_code)s,%(nickname)s,%(phone)s,%(password)s,%(expire)s,%(role)s,' \
                     '%(vip_expire)s,%(note)s);'

        user_obj = {
            'id': new_id,
            'ruizy_code': create_random_string(16),
            'nickname': username,
            'phone': phone,
            'password': hash_password,
            'expire': '2099-12-31',
            'role': 'special' if user_item.role == 'research' else 'free',
            'vip_expire': '2022-01-01',
            'note': user_item.note
        }
        db_conn = FAConnection()
        db_conn.insert(insert_sql, param=user_obj)

        return {'code': r_status.CREATED_SUCCESS, 'message': '创建用户成功!'}
    else:
        return {'code': r_status.SERVER_ERROR, 'message': '创建用户失败,原因未知!'}


# 根据类型查询用户列表
@admin_user_api.get('/', summary='根据类型查询用户列表')
async def query_user(role: str = Query(...)):
    if role not in ['superuser', 'operator', 'collector', 'research', 'normal']:
        return {'code': r_status.VALIDATE_ERROR, 'message': '查询类型错误!', 'data': []}
    sql = 'SELECT id,join_time,recent_login,user_code,username,phone,email,role,avatar,expire,admin_name ' \
          'FROM user_user WHERE role=%s AND is_active=1;'
    db = FAConnection()
    users = db.query(sql, param=[role])
    for u in users:
        u['join_time'] = u['join_time'].strftime('%Y-%m-%d %H:%M:%S')
        u['recent_login'] = u['recent_login'].strftime('%Y-%m-%d %H:%M:%S')

    return {'code': r_status.SUCCESS, 'message': '查询用户成功!', 'data': users}


# 统计各项用户的数据量
@admin_user_api.get('/count/', summary='统计各用户数量')
async def count_user():
    # 今日申请数，总申请数，今日新增，总用户，客户端用户
    # today_apply,total_apply, today_add, total_user,normal_user
    # 今日活跃数，普通活跃,失效用户数
    # today_active,normal_active,expire_count
    # 查询申请表
    apply_query = 'SELECT id,create_time FROM user_application;'
    # 查询用户表
    user_query = 'SELECT id,join_time,recent_login,expire,role FROM user_user;'
    db = FAConnection()
    apply_ret = db.query(apply_query, keep_conn=True)
    user_ret = db.query(user_query)

    apply_df = pd.DataFrame(apply_ret)
    user_df = pd.DataFrame(user_ret)

    today = datetime.datetime.today().strftime('%Y-%m-%d')
    if apply_df.empty:
        today_apply, total_apply = 0, 0
    else:
        apply_df['create_day'] = apply_df['create_time'].apply(lambda x: x.strftime('%Y-%m-%d'))
        today_apply = apply_df[apply_df['create_day'] == today].shape[0]
        total_apply = apply_df.shape[0]
    # if user_df.empty:  # 无需编写，用户已有(此处偷懒)
    user_df['join_day'] = user_df['join_time'].apply(lambda x: x.strftime('%Y-%m-%d'))
    user_df['login_date'] = user_df['recent_login'].apply(lambda x: x.strftime('%Y-%m-%d'))
    user_df['recent_login'] = user_df['recent_login'].apply(lambda x: x.strftime('%Y-%m-%d %H:%M:%S'))
    user_df['expire'] = user_df['expire'].apply(lambda x: x.strftime('%Y-%m-%d'))

    normal_df = user_df[user_df['role'] == 'normal'].copy()

    today_add = user_df[user_df['join_day'] == today].shape[0]  # 今日新增用户
    total_user = user_df.shape[0]  # 总用户
    normal_user = normal_df.shape[0]  # 客户端用户
    today_active = user_df[user_df['login_date'] == today].shape[0]  # 今日活跃数
    normal_active = normal_df[normal_df['login_date'] == today].shape[0]  # 普通活跃
    expire_count = user_df[user_df['expire'] < today].shape[0]  # 失效用户数
    response_data = [
        {'name': 'today_apply', 'name_text': '今日申请数',  'icon': '', 'color': '#2fa8f8', 'count': today_apply},
        {'name': 'total_apply', 'name_text': '总申请数',  'icon': '', 'color': '#e254fc', 'count': total_apply},
        {'name': 'today_add', 'name_text': '今日新增',  'icon': '', 'color': '#f74035', 'count': today_add},
        {'name': 'total_user', 'name_text': '总用户数',  'icon': '', 'color': '#ffd11a', 'count': total_user},
        {'name': 'normal_user', 'name_text': '普通用户',  'icon': '', 'color': '#2bd088', 'count': normal_user},
        {'name': 'today_active', 'name_text': '今日活跃',  'icon': '', 'color': '#31acf4', 'count': today_active},
        {'name': 'normal_active', 'name_text': '普通活跃',  'icon': '', 'color': '#2d8cf0', 'count': normal_active},
        {'name': 'expire_count', 'name_text': '过期数量',  'icon': '', 'color': '#e254fc', 'count': expire_count},
    ]
    return {'code': r_status.SUCCESS, 'message': '统计成功!', 'data': response_data}


# 根据统计的各类型获取详情用户信息
@admin_user_api.get('/count-detail/', summary='根据统计的各类型获取详情用户信息')
async def get_count_detail(mold: str = Query(...)):
    # 今日申请数，总申请数，今日新增，总用户，客户端用户
    # today_apply,total_apply, today_add, total_user,normal_user
    # 今日活跃数，普通活跃,失效用户数
    # today_active,normal_active,expire_count
    if mold not in ['today_apply', 'total_apply', 'today_add', 'total_user', 'normal_user', 'today_active',
                    'normal_active', 'expire_count']:
        return {'code': r_status.VALIDATE_ERROR, 'message': '参数错误,类型错误!', 'data': [], 'columns': []}
    apply_columns = [{'title': '申请日期', 'key': 'create_time'}, {'title': '申请人', 'key': 'username'},
                     {'title': '手机号', 'key': 'phone'}, {'title': '来源', 'key': 'client_code'},
                     {'title': '推荐人', 'key': 'introducer'}]
    user_columns = [{'title': '用户名', 'key': 'username'}, {'title': '加入时间', 'key': 'join_time'},
                    {'title': '最近登录', 'key': 'recent_login'}, {'title': '手机', 'key': 'phone'},
                    {'title': '有效期', 'key': 'expire', 'slot': 'useractive'}]
    # 查询数据
    apply_query = 'SELECT id,create_time,username,phone,client_code,introducer FROM user_application;'
    user_query = 'SELECT id,join_time,recent_login,username,phone,expire,role FROM user_user;'
    db = FAConnection()
    apply_ret = db.query(apply_query, keep_conn=True)
    user_ret = db.query(user_query)

    apply_df = pd.DataFrame(apply_ret)
    user_df = pd.DataFrame(user_ret)

    # 处理数据,数据库中已有数据，此处偷懒省略判空的情况
    apply_df['create_day'] = apply_df['create_time'].apply(lambda x: x.strftime('%Y-%m-%d'))
    user_df['join_day'] = user_df['join_time'].apply(lambda x: x.strftime('%Y-%m-%d'))
    user_df['login_date'] = user_df['recent_login'].apply(lambda x: x.strftime('%Y-%m-%d'))
    user_df['recent_login'] = user_df['recent_login'].apply(lambda x: x.strftime('%Y-%m-%d %H:%M:%S'))
    user_df['expire'] = user_df['expire'].apply(lambda x: x.strftime('%Y-%m-%d'))

    today = datetime.datetime.today().strftime('%Y-%m-%d')
    # 获取查询目标数据返回
    if mold == 'today_apply':  # 今日申请
        ret_df = apply_df[apply_df['create_day'] == today]
        rep = ret_df.to_dict(orient='records')
        return {'code': r_status.SUCCESS, 'message': '查询今日申请详情成功!', 'data': rep, 'columns': apply_columns}
    if mold == 'total_apply':
        rep = apply_df.to_dict(orient='records')
        return {'code': r_status.SUCCESS, 'message': '查询总申请详情成功!', 'data': rep, 'columns': apply_columns}

    if mold == 'today_add':
        ret_df = user_df[user_df['join_day'] == today]
        rep = ret_df.to_dict(orient='records')
        return {'code': r_status.SUCCESS, 'message': '查询今日新增用户成功!', 'data': rep, 'columns':user_columns }

    if mold == 'total_user':
        rep = user_df.to_dict(orient='records')
        return {'code': r_status.SUCCESS, 'message': '查询所有用户成功!', 'data': rep, 'columns': user_columns}
    if mold == 'normal_user':
        ret_df = user_df[user_df['role'] == 'normal']
        rep = ret_df.to_dict(orient='records')
        return {'code': r_status.SUCCESS, 'message': '查询普通用户成功!', 'data': rep, 'columns': user_columns}
    if mold == 'today_active':  # 今日活跃
        ret_df = user_df[user_df['login_date'] == today]
        rep = ret_df.to_dict(orient='records')
        return {'code': r_status.SUCCESS, 'message': '查询今日活跃成功!', 'data': rep, 'columns': user_columns}
    if mold == 'normal_active':  # 普通活跃
        normal_df = user_df[user_df['role'] == 'normal'].copy()
        ret_df = normal_df[normal_df['login_date'] == today]
        rep = ret_df.to_dict(orient='records')
        return {'code': r_status.SUCCESS, 'message': '查询普通活跃成功!', 'data': rep, 'columns': user_columns}
    if mold == 'expire_count':  # 失效用户数
        ret_df = user_df[user_df['expire'] < today]
        rep = ret_df.to_dict(orient='records')
        return {'code': r_status.SUCCESS, 'message': '查询失效用户成功!', 'data': rep, 'columns': user_columns}

    return {'code': r_status.SUCCESS, 'message': '参数错误!', 'data': [], 'columns': []}

